package util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import javax.naming.spi.DirStateFactory.Result;


public class DBUtil {
	private static final String className = "oracle.jdbc.OracleDriver";		//ojdbc6.jar->oracle.jdbc->OracleDriver.Class
	private static final String url = "jdbc:oracle:thin:@ZHA-ITA077-w7:1521:ita3";//包装了oracle的协议
	private static final String username = "jordan";
	private static final String psw = "123";	
	
	public static Connection createConnection(){
		Connection con = null;
		try {
			Class.forName(className);
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			con = DriverManager.getConnection(url, username, psw);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return con;
	}
	public static void close(Connection con, PreparedStatement pst, ResultSet rs){
		if(rs!= null)
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		
		if(pst != null)
			try {
				pst.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		
		if(con != null)
			try {
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
	}
	public static boolean checkUser(String username) {
		String sql = "select count(*) num from userTable where username=?";
		PreparedStatement pst = null;
		ResultSet rs = null;
		Connection con = DBUtil.createConnection();
		
		try {
			pst = con.prepareStatement(sql);
			pst.setString(1, username);
			rs = pst.executeQuery();
			if(rs.next()){
				String psw = rs.getString("num");
				if(Integer.parseInt(psw) == 1){
					return true;
				}
			}
		} catch (Exception e) {
			System.out.println("检测密码异常");
		}finally{
			DBUtil.close(con, pst, rs);
		}
		return false;
	}
	
	public static boolean checkPasswork(String username, String password) {
		String sql = "select psw from userTable where username=?";
		PreparedStatement pst = null;
		ResultSet rs = null;
		Connection con = DBUtil.createConnection();
		
		try {
			pst = con.prepareStatement(sql);
			pst.setString(1, username);
			rs = pst.executeQuery();
			if(rs.next()){
				String psw = rs.getString("psw");
				if(psw.equals(password)){
					return true;
				}
			}
		} catch (Exception e) {
			System.out.println("检测密码异常");
		}finally{
			DBUtil.close(con, pst, rs);
		}
		return false;
	}
	
	public static boolean checkIsLogin(String username) {
		String sql = "select isLogin from userTable where username=?";
		PreparedStatement pst = null;
		ResultSet rs = null;
		Connection con = DBUtil.createConnection();
		
		try {
			pst = con.prepareStatement(sql);
			pst.setString(1, username);
			rs = pst.executeQuery();
			if(rs.next()){
				Integer isLogin = Integer.parseInt(rs.getString("isLogin"));
				if(isLogin == 1){
					return true;
				}
			}
		} catch (Exception e) {
			System.out.println("检测登录异常");
		}finally{
			DBUtil.close(con, pst, rs);
		}
		return false;
	}
	
	public static boolean sign(String username, String password){
		String sql = "insert into userTable (username, psw, isLogin) values (?,?,0)";
		PreparedStatement pst = null;
		ResultSet rs = null;
		int m = 0;
		Connection con = DBUtil.createConnection();
		
		try {
			pst = con.prepareStatement(sql);
			pst.setString(1, username);
			pst.setString(2, password);
			m = pst.executeUpdate();
		} catch (SQLException e) {
			System.out.println("插入注册信息失败");
		}
		
		DBUtil.close(con, pst, rs);
		if(m == 1)
			return true;
		else
			return false;
	}
	//更新登录状态
	public static boolean login(String username) {
		String sql = "update userTable set isLogin=1 where username=?";
		PreparedStatement pst = null;
		ResultSet rs = null;
		int m = 0;
		Connection con = DBUtil.createConnection();
		
		try {
			pst = con.prepareStatement(sql);
			pst.setString(1, username);
			m = pst.executeUpdate();
		} catch (Exception e) {
			System.out.println("插入登录信息异常");
			e.printStackTrace();
		}finally{
			DBUtil.close(con, pst, rs);
		}
		if(m == 1)
			return true;
		else
			return false;
	}
	
	//更新登录状态
	public static boolean logout(String username) {
		String sql = "update userTable set isLogin=0 where username=?";
		PreparedStatement pst = null;
		ResultSet rs = null;
		int m = 0;
		Connection con = DBUtil.createConnection();
		
		try {
			pst = con.prepareStatement(sql);
			pst.setString(1, username);
			m = pst.executeUpdate();
		} catch (Exception e) {
			System.out.println("插入登出异常");
		}finally{
			DBUtil.close(con, pst, rs);
		}
		if(m == 1)
			return true;
		else
			return false;
	}
	
	public static String loadDataForWeb(){
		String loginList = "";
		String sql = "select username from userTable where islogin=1";
		PreparedStatement pst = null;
		ResultSet rs = null;
		int m = 0;
		Connection con = DBUtil.createConnection();
		
		try {
			pst = con.prepareStatement(sql);
			rs = pst.executeQuery();
			
			while(rs.next()){
				String t = rs.getString("username");
				loginList = loginList +"\\" +  t;
			}
		} catch (Exception e) {
			System.out.println("插入登出异常");
		}finally{
			DBUtil.close(con, pst, rs);
		}
		return loginList;
	}
}
